import requests
from bs4 import BeautifulSoup
import pymysql
from apscheduler.schedulers.blocking import BlockingScheduler
 
def my_job():
    # 连接到数据库
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='123456',
        database='new_test'
    )

    # 创建游标对象
    cursor = conn.cursor()

    # 爬取数据
    for i in range(0,5):
        url = f'https://www.cnhnb.com/hangqing/cdlist-2001950-0-0-0-0-{i}/'
        response = requests.get(url)
        response.encoding='utf-8'
        soup = BeautifulSoup(response.text, 'html.parser')
        div_tags = soup.find_all('div', {'class': 'quotation-content-list'})
        print("URL",url)
        for div in div_tags:
            li_tags = div.find_all('li')
            for li in li_tags:
                time = li.find('span', {'class': 'time'}).text.strip()
                product = li.find('span', {'class': 'product'}).text.strip()
                place = li.find('span', {'class': 'place'}).text.strip()
                price = li.find('span', {'class': 'price'}).text.strip()
                print('时间:', time)
                print('产品:', product)
                print('地点:', place)
                print('价格:', price)
                print("------------")
                sql = f"INSERT INTO hn_agricultural_data(time,name,place,price) values(%s,%s,%s,%s)"
                cursor.execute(sql, (time, product, place, price))
                conn.commit()

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

scheduler = BlockingScheduler()
# scheduler.add_job(my_job, 'cron', second='10') # 每分钟的第10秒执行一次
# scheduler.add_job(my_job, 'interval', seconds=10) # 每10秒执行一次
scheduler.add_job(my_job, 'cron', hour = '08',minute ='30' ,second = '00') # 每天的08:30:00执行一次
scheduler.start()